package com.teacher.util;

import java.io.File;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.common.model.Project;
import com.common.model.Score;
import com.common.model.StudentUser;
import com.common.model.TeacherUser;

/**
 * 从数据库中读取数据，并存入Excel表中
 * 
 * @author XHFSJ
 * 
 */
public class DBToExcel {

	/**
	 * 用于记录日志
	 */
	private final static Logger log = LoggerFactory.getLogger(DBToExcel.class);

	/**
	 * 将集合中的数据写入excel文件中
	 * 
	 * @param filename
	 * @param projects
	 * @return 返回生成的excel文件名
	 * @throws Exception
	 */
	public static String WriteIntoExcel(HttpServletRequest request,
			List<Project> projects,Map<String,Integer> exportCols) throws Exception {
		String filename = "projects.xls";
		String dir = request.getServletContext().getRealPath("/") + "export";
		WritableWorkbook wb = null;
		File file = new File(dir + File.separator + filename);
		File directory = new File(dir);
		try {
			if(!directory.exists()){
				directory.mkdirs();
			}
			if (file.exists()) {
				file.delete();
			}
			wb = Workbook.createWorkbook(file);
			if (wb != null) {
				WritableSheet ws = wb.createSheet("题目列表", 0);
				Label l = null;
				for (int i = 0; i < projects.size()+1; i++) {
					if(i!=0){
						Project proj = projects.get(i-1);
						StudentUser student = proj.getStudent();
						Set<String> keys = exportCols.keySet();
						for(String key : keys){
							Integer keyIndex = exportCols.get(key);
							switch(key){
							case "题目名称":
								l = new Label(keyIndex, i, proj.getName());
								ws.addCell(l);
								break;
							case "题目描述":
								WritableCellFormat wcf = new WritableCellFormat();
								wcf.setWrap(true);
								l = new Label(keyIndex, i, StringUtils.delHTMLTag(proj.getDescription()), wcf);
								ws.addCell(l);
								break;
							case "题目备注":
								l = new Label(keyIndex, i, proj.getRemark());
								ws.addCell(l);
								break;
							case "指导教师":
								l = new Label(keyIndex, i, proj.getTeacher().getName());
								ws.addCell(l);
								break;
							case "题目状态":
								l = new Label(keyIndex, i, StringUtils.propertyToStatus(proj.getStatus()));
								ws.addCell(l);
								break;
							case "评阅教师":
								TeacherUser syteacher = proj.getShengyue_teacher();
								l = new Label(keyIndex,i,syteacher==null?"":syteacher.getName());
								ws.addCell(l);
								break;
							case "学生学号":
								
							    l = new Label(keyIndex, i, student==null?"":student.getNo());
							    ws.addCell(l);
								break;
							case "学生姓名":
								l = new Label(keyIndex, i, student==null?"":student.getName());
								ws.addCell(l);
								break;
							case "学生成绩":
								Score score = proj.getScore();
								Integer num = getScoreNum(score);
								l = new Label(keyIndex, i, num.toString());
								ws.addCell(l);
								break;
							}
						}
					}else{
						for(String col : exportCols.keySet()){
							l = new Label(exportCols.get(col), i, col);
							ws.addCell(l);
						}
					}
				}
				wb.write();
				wb.close();
			}
		} catch (Exception e) {
			log.info(e.getLocalizedMessage());
			System.out.println(e.getLocalizedMessage());
			e.printStackTrace();
			return null;
		}

		String path = request.getContextPath();
		String basePath = request.getScheme() + "://" + request.getServerName()
				+ ":" + request.getServerPort() + path + "/";

		return basePath + "export" + "/" + "projects.xls";
	}
	
	/**
	 * 统计成绩
	 * @param score
	 * @return
	 */
	private static Integer getScoreNum(Score score){
		if(score==null){
			return 0;
		}
		Integer db = score.getDb_score();
		Integer sy = score.getSy_score();
		Integer py = score.getPy_score();
		if(db==null|| sy==null|| py==null){
			return 0;
		}
		else{
			return db+py+sy;
		}
	}
	/**
	 * 生成指导教师评阅表、审阅表、答辩表
	 * @param projects
	 * @return
	 * 		返回结果表的网络url地址
	 */
	public static String generateScore(List<Project> projects,HttpServletRequest request){
		TeacherUser teacher = (TeacherUser)request.getSession().getAttribute("user");
		String zd=teacher.getId()+"_result.xls";
		String dir = request.getServletContext().getRealPath("/");
		File zdf = new File(dir+File.separator+zd);
		if(zdf.exists()){
			zdf.delete();
		}
		try{
			//生成指导教师表
			WritableWorkbook wb = Workbook.createWorkbook(zdf);
			WritableSheet zdsheet = wb.createSheet("指导教师", 0);
			WritableSheet dbsheet = wb.createSheet("答辩表", 1);
			WritableSheet pysheet = wb.createSheet("评阅表", 2);
			Label l = null;
			for(int i = 0; i < projects.size()+1; i++){
				
				if(i==0){
					l = new Label(0, i, "序号");
					pysheet.addCell(l);
					zdsheet.addCell(l);
					dbsheet.addCell(l);
					l = new Label(1, i, "评阅人");
					pysheet.addCell(l);
					l = new Label(2, i, "题目");
					pysheet.addCell(l);
					l = new Label(3, i, "学生");
					pysheet.addCell(l);
					l = new Label(4, i, "评阅成绩");
					pysheet.addCell(l);
					
					l = new Label(1, i, "指导教师");
					zdsheet.addCell(l);
					l = new Label(2, i, "题目");
					pysheet.addCell(l);
					l = new Label(3, i, "学生姓名");
					zdsheet.addCell(l);
					l = new Label(4, i, "成绩");
					zdsheet.addCell(l);
					
					l = new Label(1, i, "答辩组成员");
					dbsheet.addCell(l);
					l = new Label(2, i, "题目");
					pysheet.addCell(l);
					l = new Label(3, i, "学生");
					dbsheet.addCell(l);
					l = new Label(4, i, "答辩成绩");
					dbsheet.addCell(l);
				}else{
					Project proj = projects.get(i-1);
					l = new Label(0, i, i+"");
					pysheet.addCell(l);
					zdsheet.addCell(l);
					dbsheet.addCell(l);
					//评阅表
					l = new Label(1, i, proj.getShengyue_teacher().getName());
					pysheet.addCell(l);
					l = new Label(2,i,proj.getName());
					pysheet.addCell(l);
					l = new Label(3,i,proj.getStudent().getName());
					pysheet.addCell(l);
					l = new Label(4,i,proj.getScore().getPy_score().toString());
					pysheet.addCell(l);
					//指导教师表
					l = new Label(1, i, proj.getTeacher().getName());
					zdsheet.addCell(l);
					l = new Label(2,i,proj.getName());
					zdsheet.addCell(l);
					l = new Label(3,i,proj.getStudent().getName());
					zdsheet.addCell(l);
					l = new Label(4,i,proj.getScore().getSy_score().toString());
					zdsheet.addCell(l);
					//答辩表
					l = new Label(1, i, proj.getScore().getDbmember());
					dbsheet.addCell(l);
					l = new Label(2,i,proj.getName());
					dbsheet.addCell(l);
					l = new Label(3,i,proj.getStudent().getName());
					dbsheet.addCell(l);
					l = new Label(4,i,proj.getScore().getDb_score().toString());
					dbsheet.addCell(l);
				}
				wb.write();
				wb.close();
			}
		}catch(Exception e){
			
		}
		return StringUtils.getLocation(request) + zd;
	}
}
